import os
import time

import xlsxwriter
import datetime
import mimetypes
from flask import Blueprint, request, jsonify, make_response
from flask_restful import Api,Resource
from flask import current_app as app
from uitils.comm import get_data
from uitils.comm import xtree
from uitils.myfile import myfile
from db import db1
from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
from uitils.es import Myes


firstbp = Blueprint('firstbp',__name__)
api = Api(firstbp)

class First(Resource):
    def get(self):
        str = '请求ip:' + request.remote_addr + ',请求的url' + request.url + ',错误信息' + '\n'
        app.logger.error(str)
        res = '牛逼'
        return {'cd':'lll'}

api.add_resource(First,'/text')

@firstbp.route('/zuzhi',methods=['GET','POST'])
def zuzhi():
    if request.method == 'POST':
        data = get_data()
        if int(data['pid']) == 0:
            sql = "insert into organization(name,is_q,zzsuoxie,sort,pid) values('{}',{},'{}',{},0)".format(data['name'],int(data['status']),data['zzsuoxie'],int(data['sort']))
        else:
            sql = "insert into organization(name,is_q,zzsuoxie,sort,pid) values('{}',{},'{}',{},{})".format(data['name'],int(data['status']),data['zzsuoxie'],int(data['sort']),int(data['pid']))
        db1.update(sql)
        return jsonify(({'code':200,'msg':'添加成功'}))
    else:
        name = request.args.get('name')
        if name:
            sql1 = "select * from organization where name='{}'".format(name)
            res1 = db1.find(sql1)
            return jsonify({'code': 200, 'list': res1})
        else:
            sql = "select * from organization"
            res = db1.findall(sql)
            list = xtree(res)
            return jsonify({'code':200,'list':list})


# 导入excel
@firstbp.route('/datafile',methods=['POST'])
def datafile():
    # 获取文件
    file = request.files['file']
    newname = myfile.rename(file.filename)
    # 上传文件
    file.save(os.path.join('./static/files/'+newname))
    # 打开文件读取
    name = './static/files/'+newname
    bk = open_workbook(name, encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    for i in range(2, nrows):
        row_date = sh.row_values(i)
        for j in range(0,ncols):
            print(row_date[j])
    return jsonify({'code':200,'msg':'成功'})




# 导出excel
@firstbp.route('/download', methods=["GET", "POST"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    sql = "select * from organization"
    res = db1.findall(sql)
    table.write(0, 0, "编号")
    table.write(0, 1, "名称")
    num = 1
    for i in res:
        table.write(num, 0, i['id'])
        table.write(num, 1, i['name'])
        num += 1


    workbook.close()
    filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file



@firstbp.route('/tttest')
def tttst():
    es = Myes('ttest','ttest-type')
    # 插入数据
    sql = "select * from organization limit 0,3"
    res = db1.findall(sql)
    for i in res:
        es.insert(i)

    return 'ok'

@firstbp.route('/get_tttest')
def get_tttest():
    es = Myes('ttest','ttest-type')
    query = {'match':{'name':'王嘉欣'}}
    data = es.search(query)
    print(data)
    return 'ok'


@firstbp.route('/bumen')
def bumen():
    sql = "select * from bumen"
    res = db1.findall(sql)
    return jsonify({'code':200,'list':res})


@firstbp.route('/role')
def role():
    time.sleep(1)
    sql = "select * from roles"
    res = db1.findall(sql)
    return jsonify({'code':200,'list':res})



@firstbp.route('/gangwei')
def gangwei():
    time.sleep(1)
    sql = "select * from gangwei"
    res = db1.findall(sql)
    return jsonify({'code':200,'list':res})

@firstbp.route('/uplod',methods=['POST'])
def uplod():
    file = request.files['file']
    file.save(os.path.join('./static/img/'+file.filename))
    img = 'http://localhost:5000/static/img/'+file.filename
    return jsonify({'code':200,'img':img})



@firstbp.route('/users',methods=['GET','POST'])
def users():
    if request.method == 'POST':
        data = request.form
        sql = "insert into users(password,name,bum_id,role_id,sort,level,qq,sex,mobile,is_user,job_number,gangwei_id,img,birthday,email,phone) values('{}','{}','{}',{},{},{},{},'{}','{}','{}',{},'{}',{},'{}','{}','{}','{}')".format(data['password'],data['name'],int(data['bum_id']),int(data['role_id']),int(data['sort']),int(data['level']),data['qq'], data['sex'],data['mobile'],int(data['is_user']),data['job_number'],int(data['gangwei_id']),data['img'], data['birthday'], data['email'], data['phone'])
        print(sql)
        db1.update(sql)
        return jsonify({'code':200,'msg':'添加成功'})
    else:
        sql = "select * from users"
        res = db1.findall(sql)
        return jsonify({'code': 200, 'list': res})





